using System.Data;
using System.Data.OleDb;
using System.Reflection;
using NUnit.Framework;
using ODX.Core;
using ODX.LazyUnitTester;

namespace FreeSql
{
    [Table]
    public abstract class Human : Entity
    {
        public abstract string Name { get; set; }
        public abstract IEntityList<Pet> Pets { get; }
    }

    [Table]
    public abstract class Pet : Entity
    {
        public abstract string Name { get; set;}
        public abstract Human Master { get; set; }
    }

    [TestFixture]
    public class C12_FreeSQL
    {
        [TestBody]
        static void Main()
        {
            DbDataProvider provider = 
                new DbDataProvider(
                    OleDbFactory.Instance,
                    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=sql.mdb;");
            provider.NullsPosition = NullsPosition.Minimum;

            Session session = new Session(provider,Assembly.GetExecutingAssembly());

            provider.CreateDatabase(session.Schema);

            foreach (Human human in session.All<Human>()) human.Delete();
            foreach (Pet pet in session.All<Pet>()) pet.Delete();
            session.Save();

            Human john = session.Create<Human>();
            john.Name = "John";

            Pet molly = session.Create<Pet>();
            molly.Name = "Molly";
            molly.Master = john;

            Human smith = session.Create<Human>();
            smith.Name = "Smith";

            Pet maxi = session.Create<Pet>();
            maxi.Name = "Maxi";
            maxi.Master = smith;

            Human paul = session.Create<Human>();
            paul.Name = "Paul";

            Pet lucy = session.Create<Pet>();
            lucy.Name = "Lucy";
            lucy.Master = paul;

            session.Save(SaveMode.Concurrent);

            LUT.WriteLine("let's see who are mastering M-pets:");

            DataSet guys = provider.SelectSql(@"
select h.* from Human h inner join Pet p on p.MasterID = h.ID
where p.Name like 'M%'",
                       "Human", 0, null);

            foreach ( Human h in session.Merge<Human>(guys) )
                LUT.WriteLine(h.Name);

            LUT.WriteLine("-----------------------------");

            Pager<Human> p = new Pager<Human>(session, @"
select h.* from Human h inner join Pet p on p.MasterID = h.ID
where p.Name like 'M%' and <PagerWhere>",
                                        "Name", 1, PagerFilterType.Select);
            p.Alias = "h.";

            foreach ( Human h in p )
                LUT.WriteLine(h.Name);
        }

        [Test]
        public void Test()
        {
            LUT.Execute(Main);
        }
    }
}
